***********************************************************************************
* This code obtain datasets containing population and per capita income 
* by urban and rural, for 70, 80, 90, 00, 2010, and 2016.
*
* Input: 
*	1. MSA list for urban areas
*	2. County level population and income from Census or ACS
*
* Note:
*	1. !!!!!MSA 2015 used to link with 2016 pop and income (ACS 5yr 2014-2018)!!!!!
*	2. 2016 pop and income is from ACS 5yr 2014-2018
*	3. 2010 pop and income is from ACS 5yr 2008-2012
***********************************************************************************
clear all
version 11
set more off

cd ~\data_in


***************************
***************************
* set year here
* 70, 80, 90, 00 all census
***************************
***************************
local year 2000

use data_income_pop_`year', clear
merge 1:1 fips using data_msa_fips_`year'
drop if _merge==2
replace flag_msa=0 if flag_msa==.
tab flag_msa
drop _merge
gen income_agg_2000usd = pop*income_2000usd
collapse (sum) pop income_agg_2000usd, by(flag_msa)
gen income_percapita_2000usd=income_agg_2000usd/pop
outsheet using data_income_pop_urban_rural_`year'.csv, replace names comma


***************************
***************************
* 2010 pop and income ACS
***************************
***************************
use data_income_pop_2010_acs, clear
merge 1:1 fips using data_msa_fips_2010
drop if _merge==2
replace flag_msa=0 if flag_msa==.
tab flag_msa
drop _merge
gen income_agg_2000usd = pop*income_2000usd
collapse (sum) pop income_agg_2000usd, by(flag_msa)
gen income_percapita_2000usd=income_agg_2000usd/pop
outsheet using data_income_pop_urban_rural_2010.csv, replace names comma


***************************
***************************
* 2016 pop and income ACS
* 2015 MSA used as 2016
***************************
***************************
use data_income_pop_2016_acs, clear
merge 1:1 fips using data_msa_fips_2015
drop if _merge==2
replace flag_msa=0 if flag_msa==.
tab flag_msa
drop _merge
gen income_agg_2000usd = pop*income_2000usd
collapse (sum) pop income_agg_2000usd, by(flag_msa)
gen income_percapita_2000usd=income_agg_2000usd/pop
outsheet using data_income_pop_urban_rural_2016.csv, replace names comma



